package com.kgc.dao;

import com.kgc.entity.*;
import com.kgc.untils.PageUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao extends BaseDao {


    /**
     * 用户登录查询
     *
     * @param phone
     * @param
     * @return
     */
    public User Login(String phone) {
        try {
            String sql = "SELECT  * FROM user where phone =? ";
            ResultSet rs = super.getExecuteQuery(sql, phone);
            if (rs.next()) {
                return new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getInt(6), rs.getInt(7), rs.getString(8));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return null;
    }

    /**
     * 用户新增
     * @param user
     * @return
     */
    public int addUser(User user) {
        String sql = "INSERT INTO `user`( `username`, `password`, `phone`, `email`, `sex`, `status`, `salt`) VALUES ( ?, ?, ?, ?, ?, ?, ?)";
        return super.getExecuteUpdate(sql,user.getUsername(), user.getPassword(), user.getPhone(), user.getEmail(),user.getSex(),user.getStatus(), user.getSalt());

    }
    public String selectPwd(Integer id) {
        String sql = "select password from user where id =? ";
        try {
            ResultSet rs = super.getExecuteQuery(sql,id);
            if (rs.next()){
                return rs.getString(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return null;
    }
    public int updatePwd(String pwd,Integer id) {
        String sql = "update user set password =? where id =? ";
        return super.getExecuteUpdate(sql, pwd,id);
    }

    public int totalGongGao() {
        try {
            String sql = "SELECT count(*) FROM gonggao where status=1 ";
            ResultSet rs = super.getExecuteQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    public List<Gonggao> selectGongGao(PageUtils pageUtils) {
        List<Gonggao> list = new ArrayList<>();
        try {
            String sql ="SELECT id,title,content FROM gonggao where status=1 limit ?,? ";
            ResultSet rs = super.getExecuteQuery(sql, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new Gonggao(rs.getInt(1),rs.getString(2),rs.getString(3)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    public int totalTouSu(Integer id) {
        try {
            String sql = "SELECT count(*) FROM tousu where user_id =? ";
            ResultSet rs = super.getExecuteQuery(sql,id);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    public List<Tousu> selectMYTouSu(PageUtils pageUtils, Integer id) {
        List<Tousu> list = new ArrayList<>();
        try {
            String sql ="SELECT id,content,time,result,status FROM tousu where user_id =? limit ?,? ";
            ResultSet rs = super.getExecuteQuery(sql,id,pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new Tousu(rs.getInt(1), rs.getString(2), rs.getInt(5),
                        rs.getTimestamp(3),rs.getString(4)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    public int totalWeiXiu(Integer id) {
        try {
            String sql = "SELECT count(*) FROM repair where user_id =? ";
            ResultSet rs = super.getExecuteQuery(sql,id);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    public List<Tousu> selectMYWeiXiu(PageUtils pageUtils, Integer id) {
        List<Tousu> list = new ArrayList<>();
        try {
            String sql ="SELECT id,content,time,result,status FROM repair where user_id =? limit ?,? ";
            ResultSet rs = super.getExecuteQuery(sql,id,pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new Tousu(rs.getInt(1), rs.getString(2), rs.getInt(5),
                        rs.getTimestamp(3),rs.getString(4)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }
    public List<UserRoom> selectRoomByUserId(Integer userId) {
        List<UserRoom> list = new ArrayList<>();
        try {
            String sql = "select ur.*,CONCAT(d.name,r.name) from user_room ur inner join room r on ur.room_id = r.id inner join danyuan d on d.id = r.danyuan_id where  ur.user_id=? and  ur.status = 1";
            ResultSet rs = super.getExecuteQuery(sql, userId);
            while (rs.next()) {
                list.add(new UserRoom(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getTimestamp(4), rs.getTimestamp(5),null,rs.getString(7)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }
    public List<UserCar> selectCarByUserId(Integer userId) {
        List<UserCar> list = new ArrayList<>();
        try {
            String sql = "select uc.*,c.name from user_car  uc inner join car c on uc.car_id = c.id where uc.user_id = ? and uc.status = 1";
            ResultSet rs = super.getExecuteQuery(sql, userId);
            while (rs.next()) {
                list.add(new UserCar(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getTimestamp(4), rs.getTimestamp(5), null, rs.getString(7)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    public List<UserPayment> selectPaymentById(PageUtils pageUtils, Integer id) {
        List<UserPayment> list = new ArrayList<>();
        try {
            String sql = "SELECT up.id,p.name,u.username,up.`value`,up.time, up.`status`FROM user u  " +
                    "INNER JOIN user_payment up ON u.id = up.user_id " +
                    "INNER JOIN payment p ON p.id = up.payment_id " +
                    "WHERE u.id=? limit ?,?";
            ResultSet rs = super.getExecuteQuery(sql, id,pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new UserPayment(rs.getInt(1), rs.getString(2), rs.getString(3),
                        rs.getString(4), rs.getTimestamp(5), rs.getInt(6)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }
    public int totalPayment(Integer id) {
        try {
            String sql = "SELECT COUNT(*)FROM user u " +
                    "INNER JOIN user_payment up ON u.id = up.user_id " +
                    "INNER JOIN payment p ON p.id = up.payment_id " +
                    "WHERE u.id=? ";
            ResultSet rs = super.getExecuteQuery(sql,id);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }


}
